﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;

namespace FacebookApp
{
	public class EbookService
	{

		#region[Ebook_GetbyAll]
        public static List<Ebook> Ebook_GetByAll()
		{
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    var da = (from pu in dt.Ebooks select pu).ToList();

                    return da;
                }
            }
            catch (Exception)
            {
                return null;
            }
		}
		#endregion
		#region[Ebook_GetbyId]
        public static Ebook Ebook_GetById(string Data)
		{
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    int id = int.Parse(Data);
                    var da = (from pu in dt.Ebooks where pu.Id == id select pu).First();

                    return da;
                }
            }
            catch (Exception)
            {
                return null;
            }
		}
		#endregion
		#region[Ebook_GetbyTop]
        public static List<Ebook> Ebook_GetByTop(string Top, string Where, string Order)
		{
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    string query = "Select * from [Ebook]";
                    if (!Top.Equals(""))
                        query = " Select top " + Top + " * from [Ebook] ";
                    if (!Where.Equals(""))
                        query += " where " + Where + " ";
                    if (!Order.Equals(""))
                        query += " order by " + Order;
                    
                    IEnumerable<Ebook> results = dt.ExecuteQuery<Ebook>(@query);

                    return results.ToList();
                }
            }
            catch (Exception e)
            {
                return null;
            }
		}
		#endregion
		#region[Ebook_GetbyAuthor_Id]
		public DataTable Ebook_GetByAuthor_Id(string Author_Id)
		{
            //using (var cmd = new SqlCommand("sp_Ebook_GetByAuthor_Id", GetConnection()))
            //{
            //    cmd.CommandType = CommandType.StoredProcedure;
            //    cmd.Parameters.Add(new SqlParameter("@Author_Id", Author_Id));
            //    var da = new SqlDataAdapter(cmd);
            //    var dt = new DataTable();
            //    da.Fill(dt);
            //    return dt;
            //}
		    return null;
		}
		#endregion
		#region[Ebook_GetbyCategory_Id]
		public DataTable Ebook_GetByCategory_Id(string Category_Id)
		{
            //using (var cmd = new SqlCommand("sp_Ebook_GetByCategory_Id", GetConnection()))
            //{
            //    cmd.CommandType = CommandType.StoredProcedure;
            //    cmd.Parameters.Add(new SqlParameter("@Category_Id", Category_Id));
            //    var da = new SqlDataAdapter(cmd);
            //    var dt = new DataTable();
            //    da.Fill(dt);
            //    return dt;
            //}
		    return null;
		}
		#endregion
		#region[Ebook_GetbyPublisher_Id]
		public DataTable Ebook_GetByPublisher_Id(string Publisher_Id)
		{
            //using (var cmd = new SqlCommand("sp_Ebook_GetByPublisher_Id", GetConnection()))
            //{
            //    cmd.CommandType = CommandType.StoredProcedure;
            //    cmd.Parameters.Add(new SqlParameter("@Publisher_Id", Publisher_Id));
            //    var da = new SqlDataAdapter(cmd);
            //    var dt = new DataTable();
            //    da.Fill(dt);
            //    return dt;
            //}
		    return null;
		}
		#endregion
		#region[Ebook_GetbyCreatedBy]
		public DataTable Ebook_GetByCreatedBy(string CreatedBy)
		{
            //using (var cmd = new SqlCommand("sp_Ebook_GetByCreatedBy", GetConnection()))
            //{
            //    cmd.CommandType = CommandType.StoredProcedure;
            //    cmd.Parameters.Add(new SqlParameter("@CreatedBy", CreatedBy));
            //    var da = new SqlDataAdapter(cmd);
            //    var dt = new DataTable();
            //    da.Fill(dt);
            //    return dt;
            //}
		    return null;
		}
		#endregion
		#region[Ebook_Insert]
        public static string Ebook_Insert(EbookInfo Data)
		{
           try
            {
                using (var dt = new DataClassesDataContext())
                {

                    Ebook ob = new Ebook();
                    ob.Category_Id = int.Parse(Data.Category_Id);
                    ob.Name = Data.Name;
                    ob.Image = Data.Image;
                    ob.File_Upload = Data.File_Upload;
                    ob.Promotion = Data.Promotion;
                    ob.Detail = Data.Detail;
                    ob.Price = decimal.Parse(Data.Price);
                    ob.PriceNew = decimal.Parse(Data.PriceNew);
                    ob.Status = bool.Parse(Data.Status);
                    ob.NumView = long.Parse("0");
                    ob.Size = Data.Size;
                    ob.Publisher_Id = int.Parse(Data.Publisher_Id);
                    ob.YearPub = int.Parse(Data.YearPub);
                    ob.Quantity = int.Parse(Data.Quantity);
                    ob.NumPage = int.Parse(Data.NumPage);
                    ob.Author_Id = int.Parse(Data.Author_Id);
                    ob.Weight = int.Parse(Data.Weight);
                    ob.NumLike = long.Parse("0");
                    ob.NumBought = long.Parse("0");
                    ob.isFree = bool.Parse(Data.isFree);
                    ob.isOnlineView = bool.Parse(Data.isOnlineView);
                    ob.CreatedBy = long.Parse(Data.CreatedBy);
                    ob.Order = int.Parse(Data.Order);
                    ob.CreateDate = System.DateTime.Now;
                    dt.Ebooks.InsertOnSubmit(ob);
                    dt.SubmitChanges();

                    //tra ve id vua insert
                    string query = "Select Top 1 Id From [Ebook] Order by Id DESC";
                    IEnumerable<long> results = dt.ExecuteQuery<long>(@query);
                    return results.First().ToString();
                }
            }
            catch (Exception e)
            {
                return "-1";
            }
		}
		#endregion
		#region[Ebook_Update]
        public static void Ebook_Update(EbookInfo Data)
		{
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    int id = int.Parse(Data.Id);
                    //Author da = (from au in dt.Authors where au.Id == id select au).First();
                    Ebook ob = dt.Ebooks.Single(au => au.Id == id);
                    ob.Category_Id = int.Parse(Data.Category_Id);
                    ob.Name = Data.Name;
                    ob.Image = Data.Image;
                    ob.File_Upload = Data.File_Upload;
                    ob.Promotion = Data.Promotion;
                    ob.Detail = Data.Detail;
                    ob.Price = decimal.Parse(Data.Price);
                    ob.PriceNew = decimal.Parse(Data.PriceNew);
                    ob.Status = bool.Parse(Data.Status);
                    //ob.NumView = 0;
                    ob.Size = Data.Size;
                    ob.Publisher_Id = int.Parse(Data.Publisher_Id);
                    ob.YearPub = int.Parse(Data.YearPub);
                    ob.Quantity = int.Parse(Data.Quantity);
                    ob.NumPage = int.Parse(Data.NumPage);
                    ob.Author_Id = int.Parse(Data.Author_Id);
                    ob.Weight = int.Parse(Data.Weight);
                    //ob.NumLike = 0;
                    //ob.NumBought = 0;
                    ob.isFree = bool.Parse(Data.isFree);
                    ob.isOnlineView = bool.Parse(Data.isOnlineView);
                    //ob.CreatedBy = long.Parse(Data.CreatedBy);
                    ob.Order = int.Parse(Data.Order);

                    dt.SubmitChanges();
                }
            }
            catch (Exception)
            {

            }
		}
		#endregion
		#region[Ebook_Delete]
		public static void Ebook_Delete(string Data)
		{
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    int id = int.Parse(Data);
                    //Author da = (from au in dt.Authors where au.Id == id select au).First();
                    Ebook da = dt.Ebooks.Single(au => au.Id == id);
                    dt.Ebooks.DeleteOnSubmit(da);

                    dt.SubmitChanges();
                }
            }
            catch (Exception)
            {

            }
		}
		#endregion
        #region[Ebook_Update_NumView]
        public static void Ebook_Update_NumView(string Id)
        {
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    int id = int.Parse(Id);                    
                    Ebook ob = dt.Ebooks.Single(au => au.Id == id);

                    ob.NumView += 1;

                    dt.SubmitChanges();
                }
            }
            catch (Exception)
            {

            }
        }
        #endregion
        #region[Ebook_Update_NumBought]
        public static void Ebook_Update_NumBought(string Id, string numb)
        {
            try
            {
                using (var dt = new DataClassesDataContext())
                {
                    int id = int.Parse(Id);
                    Ebook ob = dt.Ebooks.Single(au => au.Id == id);

                    ob.NumBought += int.Parse(numb);

                    dt.SubmitChanges();
                }
            }
            catch (Exception)
            {

            }
        }
        #endregion
        #region[Ebook_Search]
        public static List<Ebook> Ebook_Search(string flag, string keywork, string price1, string price2, string groupid)
        {
            string query = "";
            try
            {               
                using (var dt = new DataClassesDataContext())
                {
                    if (flag == "0")
                    {
                        query = "Select * from [Ebook] Where (Ebook.Name like N'%" + keywork + "%'"
                            + " or Ebook.Detail like N'%" + keywork + "%')"
                            + " and Price >= " + price1
                            + " and Price <= " + price2
                            + " and Category_Id in (" + groupid + ")";
                    }
                    else
                    if (flag == "1")
                    {
                        query = "Select * from [Ebook] Where Ebook.Name like N'%" + keywork + "%'"
                            + " and Price >= " + price1
                            + " and Price <= " + price2
                            + " and Category_Id in (" + groupid + ")";
                    }
                    else
                        if (flag == "2")
                        {
                            query = "Select * from [Ebook] Where Ebook.Detail like N'%" + keywork + "%'"
                                + " and Price >= " + price1
                                + " and Price <= " + price2
                                + " and Category_Id in (" + groupid + ")";
                        }

                    //WebMsgBox.Show(query);
                    IEnumerable<Ebook> results = dt.ExecuteQuery<Ebook>(@query);

                    return results.ToList();
                }
            }
            catch (Exception e)
            {
                //List<Ebook> leb = new List<Ebook>();
                //Ebook eb = new Ebook();
                //eb.Name = query + "/" + e.Message;
                //leb.Add(eb);
                //return leb;
                return null;
            }
        }
        #endregion

	}

    public class EbookInfo
    {
        public string Id { get; set; }
        public string Category_Id { get; set; }
        public string Name { get; set; }
        public string Image { get; set; }
        public string File_Upload { get; set; }
        public string Promotion { get; set; }
        public string Detail { get; set; }
        public string Price { get; set; }
        public string PriceNew { get; set; }
        public string Status { get; set; }
        public string NumView { get; set; }
        public string Size { get; set; }
        public string Publisher_Id { get; set; }
        public string YearPub { get; set; }
        public string Quantity { get; set; }
        public string NumPage { get; set; }
        public string Author_Id { get; set; }
        public string Weight { get; set; }
        public string NumLike { get; set; }
        public string NumBought { get; set; }
        public string isFree { get; set; }
        public string isOnlineView { get; set; }
        public string CreatedBy { get; set; }
        public string Order { get; set; }

    }
}